from django.db import models
from django.db import connection
import time
# Create your models here.

class HPWIPManager(models.Manager):
    COLUMN = ("ONHOLD_WIP_ID","ORDER_NR","KEYIN_DATE","CUSTOMER_PO","SERIAL_NR",\
    "ONHOLD_REASON_DESC","WAIT_MEMO","STATUS","PRODUCT_TYPE","TRAN_OWNER","TAT","CLOSED_DATE","REPAIR_ETA","ESCALATION_FEEDBACK","PART_ARRIVAL_ETA",\
    "ROOT_CAUSE","SOLUTION","CT_DT","MD_DT")
    def query_WIP(self):
        cursor = connection.cursor()
        cursor.execute("""
            SELECT hp_onhold_wip.ONHOLD_WIP_ID,hp_onhold_wip.ORDER_NR,date_format(hp_onhold_wip.KEYIN_DATE,'%Y-%m-%d %T') as KEYIN_DATE,hp_onhold_wip.CUSTOMER_PO,hp_onhold_wip.SERIAL_NR,(select distinct ONHOLD_REASON_DESC from hp_onhold_reason where hp_onhold_wip.REASON_ID=hp_onhold_reason.REASON_ID) as ONHOLD_REASON_DESC,
            hp_onhold_wip.WAIT_MEMO, hp_onhold_wip.STATUS, hp_onhold_wip.PRODUCT_TYPE, 
				hp_onhold_wip.TRAN_OWNER, hp_onhold_wip.TAT,date_format(hp_onhold_wip.CLOSED_DATE,'%Y-%m-%d %T') as CLOSED_DATE,hp_onhold_wip.REPAIR_ETA,hp_onhold_wip.ESCALATION_FEEDBACK,
                hp_onhold_wip.PART_ARRIVAL_ETA,hp_onhold_wip.ROOT_CAUSE,hp_onhold_wip.SOLUTION, date_format(hp_onhold_wip.CT_DT,'%Y-%m-%d %T') as CT_DT,date_format(hp_onhold_wip.MD_DT,'%Y-%m-%d %T') as MD_DT
             FROM hp_onhold_wip""")
        table = cursor.fetchall()
        result = []
        for row in table:
            dic = {"ONHOLD_WIP_ID":row[0], "ORDER_NR":row[1], "KEYIN_DATE":row[2],"CUSTOMER_PO":row[3],"SERIAL_NR":row[4],"ONHOLD_REASON_DESC":row[5],"WAIT_MEMO":row[6],"STATUS":row[7],"PRODUCT_TYPE":row[8],"TRAN_OWNER":row[9],"TAT":row[10],"CLOSED_DATE":row[11],"REPAIR_ETA":row[12],"ESCALATION_FEEDBACK":row[13],"PART_ARRIVAL_ETA":row[14],"ROOT_CAUSE":row[15],"SOLUTION":row[16],"CT_DT":row[17],"MD_DT":row[18],}
            result.append(dic)
        return result

    def query_WIP_by_id(self,id):
        cursor = connection.cursor()
        strsql = """
            SELECT hp_onhold_wip.ONHOLD_WIP_ID,hp_onhold_wip.ORDER_NR,date_format(hp_onhold_wip.KEYIN_DATE,'%Y-%m-%d %T') as KEYIN_DATE,hp_onhold_wip.CUSTOMER_PO,hp_onhold_wip.SERIAL_NR,(select distinct ONHOLD_REASON_DESC from hp_onhold_reason where hp_onhold_wip.REASON_ID=hp_onhold_reason.REASON_ID) as ONHOLD_REASON_DESC,
            hp_onhold_wip.WAIT_MEMO, hp_onhold_wip.STATUS, hp_onhold_wip.PRODUCT_TYPE, 
				hp_onhold_wip.TRAN_OWNER, hp_onhold_wip.TAT,date_format(hp_onhold_wip.CLOSED_DATE,'%Y-%m-%d %T') as CLOSED_DATE,hp_onhold_wip.REPAIR_ETA,hp_onhold_wip.ESCALATION_FEEDBACK,
                hp_onhold_wip.PART_ARRIVAL_ETA,hp_onhold_wip.ROOT_CAUSE,hp_onhold_wip.SOLUTION, date_format(hp_onhold_wip.CT_DT,'%Y-%m-%d %T') as CT_DT,date_format(hp_onhold_wip.MD_DT,'%Y-%m-%d %T') as MD_DT
             FROM hp_onhold_wip where ONHOLD_WIP_ID=""" +str(id)
        cursor.execute(strsql)
        table = cursor.fetchall()
        result = []
        for row in table:
            dic = {"ONHOLD_WIP_ID":row[0], "ORDER_NR":row[1], "KEYIN_DATE":row[2],"CUSTOMER_PO":row[3],"SERIAL_NR":row[4],"ONHOLD_REASON_DESC":row[5],"WAIT_MEMO":row[6],"STATUS":row[7],"PRODUCT_TYPE":row[8],"TRAN_OWNER":row[9],"TAT":row[10],"CLOSED_DATE":row[11],"REPAIR_ETA":row[12],"ESCALATION_FEEDBACK":row[13],"PART_ARRIVAL_ETA":row[14],"ROOT_CAUSE":row[15],"SOLUTION":row[16],"CT_DT":row[17],"MD_DT":row[18],}
            result.append(dic)
        return result

class Reason(models.Model):
    REASON_ID = models.AutoField(primary_key=True)
    ONHOLD_REASON_DESC = models.CharField(max_length = 50)

class WIP(models.Model):
    ONHOLD_WIP_ID = models.AutoField(primary_key=True)
    ORDER_NR = models.CharField(max_length = 10)
    KEYIN_DATE = models.DateTimeField()
    CUSTOMER_PO = models.CharField(max_length = 12)
    SERIAL_NR = models.CharField(max_length = 12)
    REASON = models.ForeignKey(Reason )
    WAIT_MEMO = models.CharField(blank=True,null=True,max_length = 250)
    STATUS = models.CharField(blank=True,null=True,max_length = 10)
    PRODUCT_TYPE = models.CharField(blank=True,null=True,max_length = 6)
    TRAN_OWNER = models.CharField(max_length = 30,blank=True,null=True)
    TAT = models.FloatField(blank=True,null=True)
    CLOSED_DATE = models.DateTimeField(blank=True,null=True)
    REPAIR_ETA = models.CharField(max_length = 50,blank=True,null=True)
    ESCALATION_FEEDBACK = models.CharField(max_length = 250,blank=True,null=True)
    PART_ARRIVAL_ETA = models.CharField(max_length = 50,blank=True,null=True)
    ROOT_CAUSE = models.CharField(max_length = 250,blank=True,null=True)
    SOLUTION = models.CharField(max_length = 250,blank=True,null=True)
    CT_DT = models.DateTimeField(auto_now_add=True)
    MD_DT = models.DateTimeField(auto_now_add=True,auto_now=True)
    DT_DT = models.DateTimeField(null=True)
    objects = HPWIPManager()
